﻿DECLARE @Var_PecipeId VARCHAR(20)
DECLARE @Var_MZNum VARCHAR(100)
DECLARE @Var_IsLastWeekFlag INT

SET @Var_ISLASTWEEKFLAG = 1
SET @Var_PecipeId = @RecipNum_In
SET @Var_MZNum = @MZNum_In

select a.keyno AS KeyNo,
       RTRIM(b.patientid) AS PatientId,
       b.mznum AS MZNum,
       b.cfnum as Recipeid,
       RTRIM(b.yskscode) as DeptId,
       RTRIM(b.ysksname) as DeptName,
       RTRIM(b.yscode) as DoctId,
       RTRIM(b.ysname) as DoctName,
       b.cfmoney as RecipeAmount,
       b.cfdate as Recipedate,
       c.fpdate AS FPDate,
       RTRIM(b.note) as Note,
       RTRIM(a.goodsno) as ItemId,
       RTRIM(d.a34) as ItemName,
       RTRIM(d.a35) as Specs,
       RTRIM(a.kmname) as ItemType,
       RTRIM(a.unitname) as Unit,
       a.ypprice as Price,
       a.totcount*A.CFCOUNT as Num,
       0 as Flag,
       b.FPNum AS FPNum,
       RTRIM(c.InvoiceNum) AS InvoiceNum,
       case isnull(c.ybflag,0) when 0 then '现金' when 1 then '医保支付' when 2 then '合作医疗' when 3 then '记帐' when 4 then '银行下卡' when 5 then '优惠' when 7 then '支票' end as YBFlag,
       RTRIM(b.PatientName) AS PatientName,
       RTRIM(b.PersonID) AS PersonId,
       RTRIM(b.Tel) AS TEL,
       RTRIM(b.PatientID) AS PatientId2,
       RTRIM(c.YBNum) AS YBNUM,
       RTRIM(b.MZDiagnose) AS MZDIAGNOSE,
       RTRIM(a.YBLBName) AS YBLBNAME,
       c.ZFMoney2 as ZFMoney,
       c.OperName as FeeOper
       ,RTRIM(C.TransNo) AS FeeTransNo
  from MZCFYPK_YZ a (nolock),MZCFINF_YZ b (nolock),
  (select * from mzinvoice (nolock) union all select * from mzinvoicehis (nolock)) c
  ,goods d(nolock)
 where a.cfnum=b.cfnum and a.mznum=b.mznum and b.FPNum=c.FPNum and a.goodsno=d.a01 and
       b.mzoperdate is not null and c.Deldate is null and
       a.MZNUM LIKE + '%' + @Var_MZNum AND 
       (
          (@Var_IsLastWeekFlag)=0 or
          (
             (@Var_IsLastWeekFlag)=1 and c.fpdate>GetDate()-3
          )
        ) AND 
       b.cfnum LIKE + '%' + @Var_PecipeId
union all
select a.keyno AS KeyNo,
       RTRIM(b.patientid) AS PatientId,
       b.mznum AS MZNum, 
       b.applynum as Recipeid,
       RTRIM(b.fskscode) as DeptId,
       RTRIM(b.fsksname) as DeptName,
       RTRIM(b.yscode) as DoctId,
       RTRIM(b.ysname) as DoctName,
       b.checkmoney as RecipeAmount,
       b.applydate as Recipedate,
       c.fpdate AS FPDate,
       RTRIM(b.note) as Note,
       RTRIM(a.checkno) as ItemId,
       RTRIM(a.checkname) as ItemName,
       '' AS Specs,
       RTRIM(a.kmname) as ItemType,
       RTRIM(a.unitname) as Unit,
       a.checkprice as Price,
       a.checkcount as Num,
       1 as Flag,
       b.FPNum AS FPNum,
       RTRIM(c.InvoiceNum) AS InvoiceNum,
       case isnull(c.ybflag,0) when 0 then '现金' when 1 then '医保支付' when 2 then '合作医疗' when 3 then '记帐' when 4 then '银行下卡' when 5 then '优惠' when 7 then '支票' end as YBFlag,
       RTRIM(b.PatientName) AS PATIENTNAME,
       '' AS PatientName,
       '' AS PersonId,
       RTRIM(b.PatientID) AS PatientId2,
       RTRIM(c.YBNum) AS YBNUM,
       RTRIM(b.Diagnose) AS DIAGNOSE,
       RTRIM(a.CheckLBName) AS YBLBNAME,
       c.ZFMoney2 as ZFMoney,
       c.OperName as FeeOper
       ,RTRIM(C.TransNo) AS FeeTransNo
  from PATIENT_APPLICATIONCHECKCODE a (nolock),PATIENT_APPLICATIONSHEET b (nolock)
  ,(select * from MZINVOICE (nolock) union all select * from MZINVOICEHIS (nolock)) c
 where a.applynum = b.applynum and b.FPNum = c.FPNum and c.Deldate is null and jzoper is not null and
       a.MZNUM LIKE + '%' + @Var_MZNum AND 
       (
          (@Var_IsLastWeekFlag)=0 or
          (
             (@Var_IsLastWeekFlag)=1 and c.fpdate>GetDate()-3
          )
        ) and
       (b.applynum+1000000000) LIKE + '%' + @Var_PecipeId
union all
select a.MZNum AS KeyNo,
       RTRIM(a.patientid) AS PatientId,
       a.mznum AS MZNum,
       a.MZNum + 2000000000 as Recipeid,
       RTRIM(a.kscode) as DeptId,
       RTRIM(a.ksname) as DeptName,
       RTRIM(a.yscode) as DoctId,
       RTRIM(a.ysname) as DoctName,
       a.XMMOney as RecipeAmount,
       a.RegisterDate as Recipedate,
       c.fpdate AS FPDate,
       '补交挂号费' as Note,
       RTRIM(a.XMCode) as ItemId,
       RTRIM(a.XMNAME) as ItemName,
       '' AS Specs,
       '' as ItemType,
       '次' as Unit,
       a.XMMONEY as Price,
       1 as Num,
       -1 as Flag,
       a.FPNum AS FPNum,
       RTRIM(c.InvoiceNum) AS InvoiceNum,
       case isnull(c.ybflag,0) when 0 then '现金' when 1 then '医保支付' when 2 then '合作医疗' when 3 then '记帐' when 4 then '银行下卡' when 5 then '优惠' when 7 then '支票' end as YBFlag,
       RTRIM(a.PatientName) AS PatientName,
       '' AS PersonId,
       '' AS TEL,
       RTRIM(a.PatientID) AS PatientId2,
       RTRIM(c.YBNum) AS YBNUM,
       '' AS MZDIAGNOSE,
       '' AS YBLBNAME,
       c.ZFMoney2 as ZFMoney,
       c.OperName as FeeOper
       ,RTRIM(C.TransNo) AS FeeTransNo
  from MZRegisterSheet a (nolock),(select * from MZINVOICE (nolock) union all select * from MZINVOICEHIS (nolock)) c
 where a.FPNum=c.FPNum and a.DelDate is null and c.Deldate is null AND
       a.MZNum LIKE @Var_MZNum AND 
       (
          (@Var_IsLastWeekFlag)=0 or
          (
             (@Var_IsLastWeekFlag)=1 and c.fpdate>GetDate()-3
          )
        ) AND
        a.MZNum LIKE @Var_MZNum AND 
       (
       ((@Var_PecipeId) = 0) or
       ((a.MZNum+2000000000) LIKE + 'like' + @Var_PecipeId)
       )
order by FPDate Desc,flag,recipeid,keyno
 